package com.anti_poverty_detection_system.springboot.util;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.util.List;
import java.util.Map;

public class DatabaseUtils {

    private JdbcTemplate jdbcTemplate;

    public DatabaseUtils(String url, String username, String password) {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public void fillRegionUpperTable() {
        // Step 1: Retrieve data from regions table
        String sql = "SELECT province, city, county, region_number FROM regions";
        List<Map<String, Object>> regions = jdbcTemplate.queryForList(sql);

        // Step 2: Insert data into region_upper table
        for (Map<String, Object> region : regions) {
            String province = (String) region.get("province");
            String city = (String) region.get("city");
            String county = (String) region.get("county");
            String regionNumberStr = (String) region.get("region_number");
            Integer regionNumber = null;

            // Convert regionNumber to Integer if not null
            if (regionNumberStr != null) {
                try {
                    regionNumber = Integer.parseInt(regionNumberStr);
                } catch (NumberFormatException e) {
                    System.err.println("Invalid region_number format for region: " + region);
                }
            }

            // Insert province
            if (province != null) {
                getOrCreateRegion(province, "无", null);

                // Insert city
                if (city != null) {
                    getOrCreateRegion(city, province, null);

                    // Insert county
                    if (county != null) {
                        getOrCreateRegion(county, city, regionNumber);
                    }
                }
            } else {
                // Special case for municipalities directly under the central government
                if (city != null && county != null) {
                    getOrCreateRegion(city, "无", null);
                    getOrCreateRegion(county, city, regionNumber);
                }
            }
        }
    }

    private void getOrCreateRegion(String regionName, String upperRegionName, Integer regionNumber) {
        // Check if the region already exists
        String checkSql = "SELECT region_upper_id FROM region_upper WHERE region_name = ? AND upper_region_name = ?";
        List<Integer> ids = jdbcTemplate.queryForList(checkSql, new Object[]{regionName, upperRegionName}, Integer.class);
        if (ids.isEmpty()) {
            // Insert the region if it doesn't exist
            String insertSql = "INSERT INTO region_upper (region_name, upper_region_name, region_number) VALUES (?, ?, ?)";
            jdbcTemplate.update(insertSql, regionName, upperRegionName, regionNumber);
        }
    }

    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/anti_poverty_detection_system";
        String username = "root";
        String password = "baizitong2003,.";

        DatabaseUtils dbUtils = new DatabaseUtils(url, username, password);
        dbUtils.fillRegionUpperTable();
    }
}
